7 任务编号：C4-007访问数据库

7.1 任务描述

前置任务：C4-006

业务模块：数据库

定义一个类，类名为AccountDatabase，在该类中，实现对数据库的增删改查等功能

任务类型：按步骤操作

7.2 详细实现步骤

7.2.1 创建AccountDatabase类

增加一个新类，类名为AccountDatabase，如下所示：

图 20 生成AccountDatabase类

在该类里面添加数据库相关操作代码，实现对数据库的访问。

首先添加DatabaseHelpler类数据成员，然后再增加构造函数，在里面对数据成员进行初始化。代码如下：

public class AccountDatabase {
    private DatabaseHelpler databaseHelpler;

    public AccountDatabase(Context context) {
        databaseHelpler=new DatabaseHelpler(context);
    }
    public void close() {
        if (databaseHelpler != null)
            databaseHelpler.close();
    }
}

7.2.2 收入类型表的增删改查

主要包括增啥改查等。

//增加收入类型
public void InsertEarningType(String strTypeName, String strNote) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(EarningType.TableAttr.SQL_Insert, new String[]{strTypeName, strNote});
}

//增加收入类型
public void InsertEarningType(String strTypeName) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(EarningType.TableAttr.SQL_Insert_Type_name, new String[]{strTypeName});
}

//删除收入类型
public void DeleteEarningTypeByTypeName(String strTypeName) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(EarningType.TableAttr.SQL_Delete_by_Type_name, new String[]{strTypeName});
}

//删除收入类型
public void DeleteEarningTypeAll() {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(EarningType.TableAttr.SQL_Delete_by_Type_name);
}

//删除收入类型
public void DeleteEarningTypeById(int id) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(EarningType.TableAttr.SQL_Delete_by_Type_name, new String[]{id + ""});
}

public List<EarningType> SelectEarningTypeAll() {
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(EarningType.TableAttr.SQL_Select_all, null);
    List<EarningType> list = new ArrayList<>();
    while (cursor.moveToNext()) {
        EarningType item = new EarningType(cursor.getInt(cursor.getColumnIndex(EarningType.TableAttr._ID)),
                cursor.getString(cursor.getColumnIndex(EarningType.TableAttr.COLUMN_TYPE_NAME)),
                cursor.getString(cursor.getColumnIndex(EarningType.TableAttr.COLUMN_NOTE)));
        list.add(item);
    }
    return list;
}

public List<String> SelectEarningTypeStringsAll(){


    List<EarningType> list=SelectEarningTypeAll();
    List<String> result=new ArrayList<>();
    for(EarningType type:list) {
        Log.v("tag",type.getTypeName());
        result.add(type.getTypeName());
    }
    return result;
}

public EarningType SelectEarningTypeById(int id) {
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(EarningType.TableAttr.SQL_Select_by_Id, new String[]{id+""});

    if (cursor.moveToNext()) {
        EarningType item = new EarningType(cursor.getInt(cursor.getColumnIndex(EarningType.TableAttr._ID)),
                cursor.getString(cursor.getColumnIndex(EarningType.TableAttr.COLUMN_TYPE_NAME)),
                cursor.getString(cursor.getColumnIndex(EarningType.TableAttr.COLUMN_NOTE)));
        return item;
    }
    return null;
}

public EarningType SelectEarningTypeByName(String name) {
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(EarningType.TableAttr.SQL_Select_by_Type_name, new String[]{name});

    if (cursor.moveToNext()) {
        EarningType item = new EarningType(cursor.getInt(cursor.getColumnIndex(EarningType.TableAttr._ID)),
                cursor.getString(cursor.getColumnIndex(EarningType.TableAttr.COLUMN_TYPE_NAME)),
                cursor.getString(cursor.getColumnIndex(EarningType.TableAttr.COLUMN_NOTE)));
        return item;
    }
    return null;
}

7.2.3 收入表的增删改查

代码如下：

//增加收入
public void InsertEarning(String strAccountDate, int nEarningTypeId, int nAmount) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Earning.TableAttr.SQL_Insert, new String[]{strAccountDate, nEarningTypeId + "", nAmount + ""});
}

public void InsertEarning(String strEarningType,String strAmount){
    Log.v("tag","strEarningType:"+strEarningType);
    Log.v("tag","strAmount:"+strAmount);

    EarningType earningType=SelectEarningTypeByName(strEarningType);
    Earning item=new Earning(0,new Date(),earningType,Integer.parseInt(strAmount));
    InsertEarning(item);
}

//增加收入
public void InsertEarning(Earning item) {
    //Gets the data repository in write mode
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    InsertEarning(sdf.format(item.getAccountDate()),item.getType().getId(),item.getAmount());

}


//删除收入
public void DeleteEarningByTypeName(String strTypeName) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Earning.TableAttr.SQL_Delete_by_Type_name, new String[]{strTypeName});
}

//删除收入
public void DeleteEarningAll() {

    //先删除收入类型数据
    DeleteEarningTypeAll();


    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Earning.TableAttr.SQL_Delete_all);
}

//删除收入
public void DeleteEarningById(int id) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Earning.TableAttr.SQL_Delete_by_Id, new String[]{id + ""});
}

//删除收入
public void DeleteEarningByTypeId(int nEarningTypeId) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Earning.TableAttr.SQL_Delete_by_Type_id, new String[]{nEarningTypeId + ""});
}


public List<Earning> SelectEarningAll() {
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(Earning.TableAttr.SQL_Select_all, null);
    List<Earning> list = new ArrayList<>();
    while (cursor.moveToNext()) {

        EarningType type=SelectEarningTypeById(cursor.getInt(cursor.getColumnIndex(Earning.TableAttr.COLUMN_FK_TYPE)));
        if(type==null)
            continue;

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Date d=sdf.parse(cursor.getString(cursor.getColumnIndex(Earning.TableAttr.COLUMN_ACCOUNT_DATE)));
            Earning item = new Earning(cursor.getInt(cursor.getColumnIndex(Earning.TableAttr._ID)),d,type,
                    cursor.getInt(cursor.getColumnIndex(Earning.TableAttr.COLUMN_AMOUNT)));
            list.add(item);

        }catch (Exception e){

        }
    }
    return list;
}

public ArrayList<Map<String, String>> getAllEarnings(){
    ArrayList<Map<String, String>> list=new ArrayList<>();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    List<Earning> earnings=SelectEarningAll();
    for(Earning e:earnings){
        Map<String, String> map = new HashMap<>();
        map.put(Earning.TableAttr._ID,e.getId()+"");
        map.put(Earning.TableAttr.COLUMN_ACCOUNT_DATE,sdf.format(e.getAccountDate()));
        map.put(Earning.TableAttr.COLUMN_AMOUNT,e.getAmount()+"");
        map.put(Earning.TableAttr.COLUMN_FK_TYPE,e.getType().getTypeName());
        list.add(map);
    }
    return list;
}

public String getEarningByDate(String date){
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(Earning.TableAttr.SQL_Select_Sum_By_Date, new String[]{date});

    if (cursor.moveToNext()) {
        return cursor.getString(0);
    }
    return "";
}

public int getEarningsCount(){
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(Earning.TableAttr.SQL_Select_Sum,null);

    if (cursor.moveToNext()) {
        return cursor.getInt(0);
    }
    return 0;
}

7.2.4 支出类型表的增删改查

代码如下：

代码如下：
//增加支出类型
public void InsertExpenditureType(String strTypeName, String strNote) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(ExpenditureType.TableAttr.SQL_Insert, new String[]{strTypeName, strNote});
}

//增加支出类型
public void InsertExpenditureType(String strTypeName) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(ExpenditureType.TableAttr.SQL_Insert_Type_name, new String[]{strTypeName});
}

//删除支出类型
public void DeleteExpenditureTypeByTypeName(String strTypeName) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(ExpenditureType.TableAttr.SQL_Delete_by_Type_name, new String[]{strTypeName});
}

//删除支出类型
public void DeleteExpenditureTypeAll() {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(ExpenditureType.TableAttr.SQL_Delete_by_Type_name);
}

//删除支出类型
public void DeleteExpenditureTypeById(int id) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(ExpenditureType.TableAttr.SQL_Delete_by_Type_name, new String[]{id + ""});
}

public List<ExpenditureType> SelectExpenditureTypeTypeAll() {
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(ExpenditureType.TableAttr.SQL_Select_all, null);
    List<ExpenditureType> list = new ArrayList<>();
    while (cursor.moveToNext()) {
        ExpenditureType item = new ExpenditureType(cursor.getInt(cursor.getColumnIndex(ExpenditureType.TableAttr._ID)),
                cursor.getString(cursor.getColumnIndex(ExpenditureType.TableAttr.COLUMN_TYPE_NAME)),
                cursor.getString(cursor.getColumnIndex(ExpenditureType.TableAttr.COLUMN_NOTE)));
        list.add(item);
    }
    return list;
}

public List<String> SelectExpenditureTypeStringsAll(){


    List<ExpenditureType> list=SelectExpenditureTypeTypeAll();
    List<String> result=new ArrayList<>();
    for(ExpenditureType type:list) {
        result.add(type.getTypeName());
    }
    return result;
}

public ExpenditureType SelectExpenditureTypeById(int id) {
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(ExpenditureType.TableAttr.SQL_Select_by_Id, new String[]{id+""});

    if (cursor.moveToNext()) {
        ExpenditureType item = new ExpenditureType(cursor.getInt(cursor.getColumnIndex(ExpenditureType.TableAttr._ID)),
                cursor.getString(cursor.getColumnIndex(ExpenditureType.TableAttr.COLUMN_TYPE_NAME)),
                cursor.getString(cursor.getColumnIndex(ExpenditureType.TableAttr.COLUMN_NOTE)));
        return item;
    }
    return null;
}

public ExpenditureType SelectExpenditureTypeByName(String name) {
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(ExpenditureType.TableAttr.SQL_Select_by_Type_name, new String[]{name});

    if (cursor.moveToNext()) {
        ExpenditureType item = new ExpenditureType(cursor.getInt(cursor.getColumnIndex(ExpenditureType.TableAttr._ID)),
                cursor.getString(cursor.getColumnIndex(ExpenditureType.TableAttr.COLUMN_TYPE_NAME)),
                cursor.getString(cursor.getColumnIndex(ExpenditureType.TableAttr.COLUMN_NOTE)));
        return item;
    }
    return null;
}

7.2.5 支出表的增删改查

//增加支出
public void InsertExpenditure(String strAccountDate, int nEarningTypeId, int nAmount) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Expenditure.TableAttr.SQL_Insert, new String[]{strAccountDate, nEarningTypeId + "", nAmount + ""});
}

public void InsertExpenditure(String strEarningType,String strAmount){
    ExpenditureType expenditure=SelectExpenditureTypeByName(strEarningType);
    Expenditure item=new Expenditure(0,new Date(),expenditure,Integer.parseInt(strAmount));
    InsertExpenditure(item);
}

//增加支出
public void InsertExpenditure(Expenditure item) {
    //Gets the data repository in write mode
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    InsertExpenditure(sdf.format(item.getAccountDate()),item.getType().getId(),item.getAmount());

}


//删除支出
public void DeleteInsertExpenditureByTypeName(String strTypeName) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Expenditure.TableAttr.SQL_Delete_by_Type_name, new String[]{strTypeName});
}

//删除支出
public void DeleteInsertExpenditureAll() {

    //先删除支出类型数据
    DeleteExpenditureTypeAll();


    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Expenditure.TableAttr.SQL_Delete_all);
}

//删除支出
public void DeleteInsertExpenditureById(int id) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Expenditure.TableAttr.SQL_Delete_by_Id, new String[]{id + ""});
}

//删除支出
public void DeleteInsertExpenditureByTypeId(int nExpenditureTypeId) {
    //Gets the data repository in write mode
    SQLiteDatabase db = databaseHelpler.getWritableDatabase();
    db.execSQL(Expenditure.TableAttr.SQL_Delete_by_Type_id, new String[]{nExpenditureTypeId + ""});
}


public List<Expenditure> SelectInsertExpenditureAll() {
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(Expenditure.TableAttr.SQL_Select_all, null);
    List<Expenditure> list = new ArrayList<>();
    while (cursor.moveToNext()) {

        ExpenditureType type=SelectExpenditureTypeById(cursor.getInt(cursor.getColumnIndex(Expenditure.TableAttr.COLUMN_FK_TYPE)));
        if(type==null)
            continue;

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Date d=sdf.parse(cursor.getString(cursor.getColumnIndex(Earning.TableAttr.COLUMN_ACCOUNT_DATE)));
            Expenditure item = new Expenditure(cursor.getInt(cursor.getColumnIndex(Expenditure.TableAttr._ID)),d,type,
                    cursor.getInt(cursor.getColumnIndex(Expenditure.TableAttr.COLUMN_AMOUNT)));
            list.add(item);

        }catch (Exception e){

        }
    }
    return list;
}

public ArrayList<Map<String, String>> getAllExpenditures(){
    ArrayList<Map<String, String>> list=new ArrayList<>();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    List<Expenditure> expenditures=SelectInsertExpenditureAll();
    for(Expenditure e:expenditures){
        Map<String, String> map = new HashMap<>();
        map.put(Expenditure.TableAttr._ID,e.getId()+"");
        map.put(Expenditure.TableAttr.COLUMN_ACCOUNT_DATE,sdf.format(e.getAccountDate()));
        map.put(Expenditure.TableAttr.COLUMN_AMOUNT,e.getAmount()+"");
        map.put(Expenditure.TableAttr.COLUMN_FK_TYPE,e.getType().getTypeName());
        list.add(map);
    }
    return list;
}

//根据日期查询支出
public String getExpenditureByDate(String date){
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(Expenditure.TableAttr.SQL_Select_Sum_By_Date, new String[]{date});

    if (cursor.moveToNext()) {
        return cursor.getString(0);
    }
    return "";
}


//支出总和
public int getExpendituresCount(){
    SQLiteDatabase db = databaseHelpler.getReadableDatabase();
    Cursor cursor = db.rawQuery(Expenditure.TableAttr.SQL_Select_Sum,null);

    if (cursor.moveToNext()) {
        return cursor.getInt(0);
    }
    return 0;
}

7 任务编号：C4-007访问数据库

results matching ""

No results matching ""